﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL
{
   public class NoticeDAL
    {
        SqlConnection str = new SqlConnection("Data Source=PC201609040948;Initial Catalog=TianTianDai_Db;Persist Security Info=True;User ID=sa;pwd=123456");
        /// <summary>
        /// 发标
        /// </summary>
        /// <returns></returns>
        public List<Notice> ShowNotice()
        {
            str.Open();
            string sql = "select * from Notice";
            SqlDataAdapter adap = new SqlDataAdapter(sql, str);
            DataTable ta = new DataTable();
            adap.Fill(ta);
            str.Close();
            List<Notice> listNotice = new List<Notice>();
            foreach (DataRow item in ta.Rows)
            {
                Notice n = new Notice();
                n.NoticeContent = item["NoticeContent"].ToString();
                n.NoticeId = Convert.ToInt32(item["NoticeId"]);
                n.NoticeName = item["NoticeName"].ToString();
                n.NoticePerson = item["NoticePerson"].ToString();
                n.NoticeTime = Convert.ToDateTime(item["NoticeTime"]);
                listNotice.Add(n);
            }
            return listNotice;
        }
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteNotice(int id)
        {
            str.Open();
            string sql = "delete signs where NoticeId='" + id + "'";
            SqlCommand com = new SqlCommand(sql, str);
            var i = com.ExecuteNonQuery();
            return i;
        }
        /// <summary>
        /// 添加信息
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public int Notic_Add(Model.Notice p)
        {
            str.Open();
            string sql = "insert into Notice values('" + p.NoticeContent + "','" + p.NoticeName + "','" + p.NoticePerson + "','" + p.NoticeTime + "')";
            SqlCommand com = new SqlCommand(sql, str);
            var i = com.ExecuteNonQuery();
            return i;
        }
        /// <summary>
        /// 显示详细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Notice ShowNoticeX(int id)
        {
            str.Open();
            string sql = "select * from Notice where NoticeId='" + id + "'";
            SqlDataAdapter adap = new SqlDataAdapter(sql, str);
            DataTable ta = new DataTable();
            adap.Fill(ta);
            str.Close();
            List<Notice> listNotice = new List<Notice>();
            foreach (DataRow item in ta.Rows)
            {
                Notice n = new Notice();
                n.NoticeContent = item["NoticeContent"].ToString();
                n.NoticeId = Convert.ToInt32(item["NoticeId"]);
                n.NoticeName = item["NoticeName"].ToString();
                n.NoticePerson = item["NoticePerson"].ToString();
                n.NoticeTime = Convert.ToDateTime(item["NoticeTime"]);
                listNotice.Add(n);
            }
            return listNotice.FirstOrDefault();
        }
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int UpdateNotice(Notice data)
        {
            str.Open();
            string sql = "update Notice set  NoticeContent='" + data.NoticeContent + "',NoticeName='" + data.NoticeName + "',NoticePerson='" + data.NoticePerson + "',NoticeTime='" + data.NoticeTime + "' where ProjectId='" + data.NoticeId + "'";
            SqlCommand com = new SqlCommand(sql, str);
            int i = com.ExecuteNonQuery();
            return i;
        }
        /// <summary>
        /// 搜索
        /// </summary>
        /// <param name="pp"></param>
        /// <returns></returns>
        public List<Notice> SeleteNotice(string name)
        {
            str.Open();
            string sql = "selete NoticeId,EarningsId,NoticeContent,NoticeName,NoticePerson,NoticeTime,NoticeTime from Project where NoticeName like '" + name + "'";
            SqlDataAdapter adap = new SqlDataAdapter(sql, str);
            DataTable ta = new DataTable();
            adap.Fill(ta);
            List<Notice> listNotice = new List<Notice>();
            foreach (DataRow item in ta.Rows)
            {
                Notice n = new Notice();
                n.NoticeContent = item["NoticeContent"].ToString();
                n.NoticeId = Convert.ToInt32(item["NoticeId"]);
                n.NoticeName = item["NoticeName"].ToString();
                n.NoticePerson = item["NoticePerson"].ToString();
                n.NoticeTime = Convert.ToDateTime(item["NoticeTime"]);
                listNotice.Add(n);
            }
            return listNotice;
        }
    }
}
